package com.basicsOperation.controller;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.basicsOperation.service.BasicsOperationService;
import com.basicsOperation.vo.CurrencyVo;
import com.basicsOperation.vo.SalesDetailedVo;
import com.mysql.fabric.xmlrpc.base.Array;
import com.util.DateUtil;
import com.util.ExcelUitl;



@RequestMapping("salesDetailed")
@Controller
public class SalesDetailedController {
	
	@Autowired
	private BasicsOperationService basicsOperationServiceImp;
	
	//log4j日志
    private Logger log = Logger.getLogger(this.getClass().getName());
	
	  
	  /**
	    * 查询销售明细数据
	    * @Parameters:
	    * @return: 销售明细数据
	 * @throws Exception 
	    */
	  @RequestMapping("selSalesDetailed")
	  @ResponseBody
	 public Map<String,Object> selSalesDetailed(HttpServletRequest request,HttpServletResponse response) throws Exception{
			Integer page =Integer.parseInt(request.getParameter("page"));
			Integer rows =Integer.parseInt(request.getParameter("rows"));
			String createTime_min=request.getParameter("createTime_min");
			String createTime_max=request.getParameter("createTime_max");
			log.info("开始日期"+createTime_min+"结束日期"+createTime_max);
			   Map<String, Object> map=new HashMap<String, Object>();
			     map.put("createTime_min", new DateUtil().dateOperation(createTime_min,"D",-1));
			     map.put("createTime_max", new DateUtil().dateOperation(createTime_max,"D",1));
			     map.put("pageNum", page);
			     map.put("pageSize", rows);
			   List<SalesDetailedVo> salesList=basicsOperationServiceImp.selSalesDetailed(map);			
			   Map<String, Object> jsonMap=new HashMap<String, Object>();
			   jsonMap.put("rows", salesList);
			   jsonMap.put("total",500);
		    /*	   
			   LinkedHashMap<String, String> fieldMap =new LinkedHashMap<String, String>();
			   fieldMap.put("开始时间","2016-09-01");
			   fieldMap.put("结束时间","2016-09-10");
			   fieldMap.put("门店账号", "123456789");
			   fieldMap.put("表名1", "xw_browsing_record");
			   fieldMap.put("表名2", "xw_login_record");
			   fieldMap.put("表名3", "xw_visit_record");
			   List<SalesDetailedVo> salesDetailedVos=setFieldValueByName(salesList,fieldMap);   
			   */
			   
			   
			   
		       return jsonMap;
			
	 }
	
		 /**
	    * 导出销售明细数据
	    * @Parameters:导出条件
	    * @return: 销售明细数据
	    */
	 @ResponseBody
	 @RequestMapping("downloadSelSalesDetailed")
	 public String downloadSelSalesDetailed(HttpServletRequest request,HttpServletResponse response){
			String createTime_min=request.getParameter("createTime_min");
			String createTime_max=request.getParameter("createTime_max");
		 Map<String, Object> map=new HashMap<String, Object>();
	     map.put("createTime_min", new DateUtil().dateOperation(createTime_min,"D",-1));
	     map.put("createTime_max", new DateUtil().dateOperation(createTime_max,"D",1));
	     //数据源
	     List<SalesDetailedVo> salesList=basicsOperationServiceImp.selSalesDetailed(map);
	     //字段名和字段值对应
	     LinkedHashMap<String, String> fieldMap =new LinkedHashMap<String, String>();
	     fieldMap.put("recordDate", "业务日期");
	     fieldMap.put("moOrderType", "订单类型");
	     fieldMap.put("moOrderId", "订单单号");
	     fieldMap.put("moOrderTotalMoney", "订单总金额");
	     fieldMap.put("empCode", "员工编号");
	     fieldMap.put("empName", "员工姓名");
	     fieldMap.put("proId", "商品ID");
	     fieldMap.put("proName", "商品名称");
	     fieldMap.put("proSalesPrice", "商品售价");
	     fieldMap.put("proCostPrice", "商品成本");
	     //文件名
	     String fileName="销售明细报表";	    
		try {
			ExcelUitl.listToExcel(salesList,fieldMap,fileName, response,request);			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}							    	     
		 return "s";
	 } 
	  	  
	  
	  /**
	    * 导出销售明细数据01
	    * @Parameters:导出条件
	    * @return: 销售明细数据
	    */
	 @ResponseBody
	 @RequestMapping("downloadSelSalesDetailed01")
	 public String downloadSelSalesDetailed01(HttpServletRequest request,HttpServletResponse response){
			String createTime_min=request.getParameter("createTime_min");
			String createTime_max=request.getParameter("createTime_max");
		 Map<String, Object> map=new HashMap<String, Object>();
	     map.put("createTime_min", new DateUtil().dateOperation(createTime_min,"D",-1));
	     map.put("createTime_max", new DateUtil().dateOperation(createTime_max,"D",1));
	     List<SalesDetailedVo> salesList=basicsOperationServiceImp.selSalesDetailed(map);
	  
	     //创建HSSFWorkbook对象(excel的文档对象)
	     HSSFWorkbook wb = new HSSFWorkbook();
	     //建立新的sheet对象（excel的表单）
	     HSSFSheet sheet = wb.createSheet("成绩表"); 
	     //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
	     HSSFRow row = sheet.createRow(0);
	     //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
	     HSSFCell cell=row.createCell(0);
	     //设置单元格内容      
	     cell.setCellValue("学员考试成绩一览表");
	     //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
	     //sheet.addMergedRegion(new CellRangeAddress(0,0,0,3));
	     //在sheet里创建第二行  
	     HSSFRow row2=sheet.createRow(1);
	     //创建单元格并设置单元格内容  
	      row2.createCell(0).setCellValue("姓名");  
	      row2.createCell(1).setCellValue("班级");      
	      row2.createCell(2).setCellValue("笔试成绩");
	      row2.createCell(3).setCellValue("机试成绩");      
	      //在sheet里创建第三行  
	      HSSFRow row3=sheet.createRow(2);  
	      row3.createCell(0).setCellValue("李明");  
	      row3.createCell(1).setCellValue("As178");  
	      row3.createCell(2).setCellValue(87);      
	      row3.createCell(3).setCellValue(78); 
	      //输出Excel文件  
	     try {
			 OutputStream output=response.getOutputStream();  
		      response.reset();  
		      response.setHeader("Content-disposition", "attachment; filename=details.xls");  
		      response.setContentType("application/msexcel"); 
			 wb.write(output);
		     output.flush();
		} catch (Exception e) {
			e.printStackTrace();
		}
	      
		 return "s";
	 }
	
	  /**
	    * 导出销售明细数据02
	    * @Parameters:导出条件
	    * @return: 销售明细数据
	    */
	 @ResponseBody
	 @RequestMapping("downloadSelSalesDetailed02")
	 public String downloadSelSalesDetailed02(HttpServletRequest request,HttpServletResponse response){
			String createTime_min=request.getParameter("createTime_min");
			String createTime_max=request.getParameter("createTime_max");
		 Map<String, Object> map=new HashMap<String, Object>();
	     map.put("createTime_min", new DateUtil().dateOperation(createTime_min,"D",-1));
	     map.put("createTime_max", new DateUtil().dateOperation(createTime_max,"D",1));
	     List<SalesDetailedVo> salesList=basicsOperationServiceImp.selSalesDetailed(map);
	    //获得输出流，该输出流的输出介质是客户端浏览器
	     try {
			OutputStream output=response.getOutputStream();
			//log.info("ss"+output);
			response.reset();
			response.setHeader("Content-disposition","attachment;filename=temp.xls");
			response.setContentType("application/msexcel");
			//创建可写入的Excel工作薄，且内容将写入到输出流，并通过输出流输出给客户端浏览
		    WritableWorkbook wk=Workbook.createWorkbook(output);
		    //创建可写入的Excel工作表
		    WritableSheet sheet=wk.createSheet("成绩表", 0);
		    
		    //把单元格（column, row）到单元格（column1, row1）进行合并。
		    //mergeCells(column, row, column1, row1);
		    sheet.mergeCells(0,0, 4,0);//单元格合并方法
		    
		    //创建WritableFont 字体对象，参数依次表示黑体、字号12、粗体、非斜体、不带下划线、亮蓝色
		    WritableFont titleFont=new WritableFont(WritableFont.createFont("黑体"),12,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.LIGHT_BLUE);
		   
		    //创建WritableCellFormat对象，将该对象应用于单元格从而设置单元格的样式
		    WritableCellFormat titleFormat=new WritableCellFormat();

		    //设置字体格式
		    titleFormat.setFont(titleFont);

		    //设置文本水平居中对齐
		    titleFormat.setAlignment(Alignment.CENTRE);

		    //设置文本垂直居中对齐
		    titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

		    //设置背景颜色
		    titleFormat.setBackground(Colour.GRAY_25);

		    //设置自动换行
		    titleFormat.setWrap(true);
			
		    //添加Label对象，参数依次表示在第一列，第一行，内容，使用的格式 
		    Label lab_00=new Label(0,0,"学员考试成绩一览表",titleFormat);
			
		   //将定义好的Label对象添加到工作表上，这样工作表的第一列第一行的内容为‘学员考试成绩一览表’并应用了titleFormat定义的样式
		    sheet.addCell(lab_00);
		    
		    WritableCellFormat cloumnTitleFormat=new WritableCellFormat();
		    cloumnTitleFormat.setFont(new WritableFont(WritableFont.createFont("宋体"),10,WritableFont.BOLD,false));
		    cloumnTitleFormat.setAlignment(Alignment.CENTRE);
		    Label lab_01=new Label(0,1,"姓名",cloumnTitleFormat);
		    Label lab_11=new Label(1,1,"班级",cloumnTitleFormat);
		    Label lab_21=new Label(2,1,"笔试成绩",cloumnTitleFormat);
		    Label lab_31=new Label(3,1,"上机成绩",cloumnTitleFormat);
		    Label lab_41=new Label(4,1,"考试日期",cloumnTitleFormat);
		    sheet.addCell(lab_01);
		    sheet.addCell(lab_11);
		    sheet.addCell(lab_21);
		    sheet.addCell(lab_31);
		    sheet.addCell(lab_41);
		    sheet.addCell(new Label(0,2,"李明"));
		    sheet.addCell(new Label(1,2,"As178"));		    
		    //定义数字格式
		    NumberFormat nf=new NumberFormat("0.00");
		    WritableCellFormat wcf=new WritableCellFormat(nf);
		    //类似于Label对象，区别Label表示文本数据，Number表示数值型数据
		     Number numlab_22=new Number(2,2,78,wcf);
		    sheet.addCell(numlab_22);
		    sheet.addCell(new Number(3,2,87,new WritableCellFormat(new NumberFormat("#.##") )));
		    //定义日期格式
		    DateFormat df=new DateFormat("yyyy-MM-dd hh:mm:ss");   
		    //创建WritableCellFormat对象
		    WritableCellFormat datewcf=new WritableCellFormat(df);
		    //类似于Label对象，区别Label表示文本数据，DateTime表示日期型数据
		    DateTime dtLab_42=new DateTime(4,2,new Date(),datewcf);
		    sheet.addCell(dtLab_42);   
		    //将定义的工作表输出到之前指定的介质中（这里是客户端浏览器）
		    wk.write();
		    //操作完成时，关闭对象，释放占用的内存空间   
		    wk.close();
		    //加下划线这部分代码是B/S模式中采用的输出方式, 而不是输出到本地指定的磁盘目录。
		    //该代码表示将temp.xls的Excel文件通过应答实体（response）输出给请求的客户端浏览器，下载到客户端本地（保存或直接打开）。
		    //若要直接输出到磁盘文件可采用下列代码替换加下划线这部分代码
		   // File file=new File("D://temp.xls");
		    //WritableWorkbook wwb = Workbook.createWorkbook(file); 
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	    	     
		 return "s";
	 }
	 
	 /**
	  * 根据传入的字段名对实体类对应的字段赋值
	  * @param list
	  * @param LinkedHashMap<String, String> fieldMap
	  * map中key值必须要有： 开始时间, 结束时间, 门店账号
	  *     至少有一对： key--字段名
	  *              value-- 字段名对应的表名           
	  * @return
	 * @throws IllegalAccessException 
	 * @throws Exception 
	  */
	 public <T> List<T> setFieldValueByName(List<T> list,LinkedHashMap<String, String> fieldMap) throws Exception{      
		 	 
		 boolean condition=false;		 		 
		 //判断map中是否有必须的字段
		  if(fieldMap.containsKey("开始时间")){		 
			  if (fieldMap.containsKey("结束时间")) {				  
				if (fieldMap.containsKey("门店账号字段")) {
					if (fieldMap.size()>3) {
						condition=true;
					}else {
						System.out.println("无:表名和字段的对应");
					}					
				}else {
					System.out.println("无:门店账号");
				}
			}else {
				System.out.println("无:结束时间");
			}
		  }else {			  
			System.out.println("无:开始时间");
		  }	
		  if (condition) {	
			  if (fieldMap.get("查询类型").equals("浏览")) {							 
				  //获取所有的门店账号
				  List<String> allList= ExcelUitl.getFieldByAllValue(list, fieldMap.get("门店账号字段"));          
				  //查询参数Map
				  Map<String, Object> parameter=new HashMap<String,Object>();
				  parameter.put("startDate",fieldMap.get("开始时间"));
				  parameter.put("endDate",fieldMap.get("结束时间"));  
				  parameter.put("list", allList);
				  //最终map
				  Map<String, Map<String, String>> valueMap=new HashMap<String, Map<String,String>>();
				  //门店账号-最后浏览时间Map
				  Map<String,String> storeAndDate=new HashMap<String, String>();
				//门店账号-浏览次数Map
				  Map<String,String> storeAndCount=new HashMap<String, String>();
				//门店账号-最后回访时间Map
				  Map<String,String> storeAndVisit=new HashMap<String, String>();
				  //查询门店的信息
				  List<CurrencyVo> storeInfo=basicsOperationServiceImp.selStoreBrowseInfo(parameter);				  
				  for (CurrencyVo currencyVo : storeInfo) {
					  storeAndDate.put(currencyVo.getColumn01(), currencyVo.getColumn02());
					  storeAndCount.put(currencyVo.getColumn01(), currencyVo.getColumn03());
					  storeAndVisit.put(currencyVo.getColumn01(), currencyVo.getColumn04());
				   }
				  valueMap.put(fieldMap.get("最后浏览时间字段"), storeAndDate);
				  valueMap.put(fieldMap.get("浏览次数字段"), storeAndCount);
				  valueMap.put(fieldMap.get("最后回访时间字段"), storeAndVisit);
				  
				  List<T> list2=ExcelUitl.setFieldOnValue(list, valueMap, fieldMap.get("门店账号字段"));	
				  return list2;
			  }			  			  
		  }	  				  
	  return list;
	 }	 		 	 	 	 	 	
}
